package com.galaxy.tools.utils;

import com.galaxy.tools.annotation.ExcelCell;
import org.apache.commons.beanutils.MethodUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * The <code>ExcelUtils</code> 是一个excel导入辅助类
 * <p>
 * <p>
 * ExcelUtils使用举例
 * </p>
 * <p>
 * <pre class="code">
 * <p>创建一个实体类，标注对应excel中的头部名称</p>
 * public class Demo {
 *
 * @author galaxy 2016年5月13日 下午3:02:37 modified by :
 * @ExcelCell(value = "demo名称",isNull=true)
 * private String demoName;
 * public String getDemoName(){
 * return this.demoName;
 * }
 * public void setDemoName(String demoName){
 * this.demoName = demoName;
 * }
 * }
 * </pre>
 * <p>
 * <pre class="code">
 * <p>创建一个实体类，标注对应excel中的头部名称</p>
 * Map<String,String> mapExcellIndex = ExcelUtils.mapExcelIndex(Demo.class,sheet.get(0));
 * for (int i = 1; i <= sheet.getLastRowNum(); i++) {
 * Demo dto = (Demo)ExcelUtils.parseObject(sheet.getRow(i), mapExcellIndex, Demo.class);
 * 相关业务代码
 * }
 * </pre>
 * <p>第二种方式</p>
 * <pre class="code">
 * List<Demo> demoes = ExcelUtils.mapExcelIndex(Demo.class,inputStream,0);
 * </pre>
 * <p>
 * 处理导入结果
 * </p>
 */
public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 格式化单元格 如,0.00,m/d/yy去HSSFDataFormat或XSSFDataFormat里面找
     *
     * @param cellStyle
     * @param fmt
     *
     * @return
     */
    public static CellStyle setCellFormat(CreationHelper helper, CellStyle cellStyle, String fmt) {
        // 还可以用其它方法创建format
        cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt));
        return cellStyle;
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     *
     * @return
     */
    public static Object getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue().trim();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return Boolean.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            DecimalFormat df = new DecimalFormat("#");
            return String.valueOf(df.format(cell.getNumericCellValue())).trim();
        }
        return "";
    }

    public static Map<Integer, String> mapExcelIndex(Class clazz, Row row) {
        Map<Integer, String> indexMap = new HashMap<Integer, String>();
        Map<String, Field> map = excelMapFields(clazz);

        int totalCell = row.getLastCellNum();
        Cell cell = null;
        for (int i = 0; i < totalCell; i++) {
            cell = row.getCell(i);
            Object cellValue = getCellValue(cell);
            if (map.containsKey(cellValue)) {
                indexMap.put(cell.getColumnIndex(), map.get(cellValue).getName());
            }
        }

        return indexMap;
    }

    /**
     * @param clazz       要转的OO
     * @param inputStream 文件输入流
     * @param sheetIndex  sheet头的行号，默认0
     *
     * @return
     */
    public static <T> List<T> mapExcelObj(Class<T> clazz, InputStream inputStream, Integer sheetIndex) {
        sheetIndex = sheetIndex == null ? 0 : sheetIndex;
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            try {
                wb = new HSSFWorkbook(inputStream);
            } catch (Exception e1) {
                e1.printStackTrace();
                throw new RuntimeException("解析出错");
            }
        } finally {
            try {
                if (!ObjectUtils.isEmpty(wb)) {
                    wb.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        Sheet sheet = wb.getSheetAt(0);
        Map<Integer, String> mapExcellIndex = mapExcelIndex(clazz, sheet.getRow(sheetIndex));
        List<T> tList = new ArrayList<T>();
        for (int i = sheetIndex+1; i <= sheet.getLastRowNum(); i++) {
            T t = (T) ExcelUtils.parseObject(sheet.getRow(i), mapExcellIndex, clazz);
            tList.add(t);
        }
        return tList;
    }

    /**
     * @param clazz       要转的OO
     * @param file        文件
     * @param sheetIndex  sheet头的行号，默认0
     *
     * @return
     */
    public static <T> List<T> mapExcelObj(Class<T> clazz, MultipartFile file, Integer sheetIndex) {
        sheetIndex = sheetIndex == null ? 0 : sheetIndex;
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(file.getInputStream());
        } catch (Exception e) {
            try {
                wb = new HSSFWorkbook(file.getInputStream());
            } catch (Exception e1) {
                e1.printStackTrace();
                throw new RuntimeException("解析出错");
            }
        } finally {
            try {
                if (!ObjectUtils.isEmpty(wb)) {
                    wb.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        Sheet sheet = wb.getSheetAt(0);
        Map<Integer, String> mapExcellIndex = mapExcelIndex(clazz, sheet.getRow(sheetIndex));
        List<T> tList = new ArrayList<T>();
        for (int i = sheetIndex+1; i <= sheet.getLastRowNum(); i++) {
            T t = (T) ExcelUtils.parseObject(sheet.getRow(i), mapExcellIndex, clazz);
            tList.add(t);
        }
        return tList;
    }

    /**
     * 获取class的 包括父类的
     *
     * @param clazz
     *
     * @return
     */
    private static Map<String, Field> excelMapFields(Class clazz) {
        Map<String, Field> map = new HashMap<String, Field>();
        Field[] fields;
        do {
            fields = clazz.getDeclaredFields();
            for (Field f : fields) {
                ExcelCell cell = f.getAnnotation(ExcelCell.class);
                if (!ObjectUtils.isEmpty(cell)) {
                    map.put(cell.value(), f);
                }
            }
            clazz = clazz.getSuperclass();
        } while (clazz != Object.class && clazz != null);
        return map;
    }

    private static Map<String, Field> fieldsMap(Class clazz) {
        Map<String, Field> map = new HashMap<String, Field>();
        Field[] fields;
        do {
            fields = clazz.getDeclaredFields();
            for (Field f : fields) {
                ExcelCell cell = f.getAnnotation(ExcelCell.class);
                if (!ObjectUtils.isEmpty(cell)) {
                    map.put(f.getName(), f);
                }
            }
            clazz = clazz.getSuperclass();
        } while (clazz != Object.class && clazz != null);
        return map;
    }

    public static Object parseObject(Row row, Map<Integer, String> indexMap, Class clazz) {
        Map<String, Field> mapFields = fieldsMap(clazz);
        Object instance = null;
        try {
            instance = clazz.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }

        for (Integer index : indexMap.keySet()) {
            Cell cell = row.getCell(index);
            Field field = mapFields.get(indexMap.get(index));
            ExcelCell ec = field.getAnnotation(ExcelCell.class);
            Object cellValue = getCellValue(field.getType().toString(), cell, ec);
//			if (!ObjectUtils.isEmpty(ec)) {
//				boolean isNull = ec.isNull();
//				if (!isNull) {
//					if (StringUtils.isEmpty(cellValue)) {
//						throw new RuntimeException(ec.value() + "字段不能为空");
//					}
//				}
//			}
            Method method = MethodUtils.getAccessibleMethod(clazz, "set" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1), field.getType());
            try {
                method.invoke(instance, getValueByType(field.getType().toString(), cellValue, ec));
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return instance;
    }

    /**
     * 根据返回类型获取返回值
     *
     * @param xclass
     * @param result
     * @param ec
     *
     * @return
     */
    private static Object getValueByType(String xclass, Object result, ExcelCell ec) {
        try {
            if (ObjectUtils.isEmpty(result)) {
                return null;
            }
            if ("class java.util.Date".equals(xclass)) {
                return result;
            }
            if ("class java.lang.Boolean".equals(xclass) || "boolean".equals(xclass)) {
                return Boolean.valueOf(String.valueOf(result));
            }
            if ("class java.lang.Double".equals(xclass) || "double".equals(xclass)) {
                return Double.valueOf(String.valueOf(result));
            }
            if ("class java.lang.Long".equals(xclass) || "long".equals(xclass)) {
                try {
                    return Long.valueOf(String.valueOf(result));
                } catch (Exception e) {
                    // 格式错误的时候,就用double,然后获取Int值
                    return Double.valueOf(String.valueOf(result)).longValue();
                }
            }
            if ("class java.lang.Float".equals(xclass) || "float".equals(xclass)) {
                return Float.valueOf(String.valueOf(result));
            }
            if ("class java.lang.Integer".equals(xclass) || "int".equals(xclass)) {
                try {
                    return Integer.valueOf(String.valueOf(result));
                } catch (Exception e) {
                    // 格式错误的时候,就用double,然后获取Int值
                    return Double.valueOf(String.valueOf(result)).intValue();
                }
            }
            if ("class java.math.BigDecimal".equals(xclass)) {
                return new BigDecimal(String.valueOf(result));
            }
            if ("class java.lang.String".equals(xclass)) {
                // 针对String 类型,但是Excel获取的数据却不是String,比如Double类型,防止科学计数法
                if (result instanceof String) {
                    return result;
                }
                // double类型防止科学计数法
                if (result instanceof Double) {
                    return (Double) result;
                }
                return String.valueOf(result);
            }
            return result;
        } catch (Exception e) {
            throw new RuntimeException("", e);
        }
    }

    /**
     * 获取单元格内的值
     *
     * @param xclass
     * @param cell
     * @param ec
     *
     * @return
     */
    private static Object getCellValue(String xclass, Cell cell, ExcelCell ec) {
        if (cell == null) {
            return "";
        }
        Object result = null;
        if ("class java.util.Date".equals(xclass) || "class java.sql.Date".equals(xclass) || ("class java.sql.Time").equals(xclass) || ("class java.sql.Timestamp").equals(xclass)) {
            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                result = cell.getDateCellValue();
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                result = getDateData(ec, cell.getStringCellValue());
            }
            if (("class java.sql.Date").equals(xclass)) {
                result = new java.sql.Date(((Date) result).getTime());
            }
            if (("class java.sql.Time").equals(xclass)) {
                result = new Time(((Date) result).getTime());
            }
            if (("class java.sql.Timestamp").equals(xclass)) {
                result = new Timestamp(((Date) result).getTime());
            }
        } else {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        if ("class java.lang.String".equals(xclass)) {
                            result = formateDate(ec, cell.getDateCellValue());
                        }
                    } else {
                        result = readNumericCell(cell);
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                case Cell.CELL_TYPE_ERROR:
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    try {
                        result = readNumericCell(cell);
                    } catch (Exception e1) {
                        try {
                            result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString();
                        } catch (Exception e2) {
                            throw new RuntimeException("获取公式类型的单元格失败", e2);
                        }
                    }
                    break;
                default:
                    break;
            }
        }
        return result;
    }

    private static String formateDate(ExcelCell ec, Date value) {
        if (!StringUtils.isEmpty(ec.dataFormatter()) && value != null) {
            SimpleDateFormat format = new SimpleDateFormat(ec.dataFormatter());
            return format.format(value);
        }
        return null;
    }

    private static Object readNumericCell(Cell cell) {
        Object result = null;
        double value = cell.getNumericCellValue();
        if (((int) value) == value) {
            result = (int) value;
        } else {
            result = value;
        }
        return result;
    }

    /**
     * 获取日期类型数据
     *
     * @param ec
     * @param value
     *
     * @return
     *
     * @Author JueYue
     * @date 2013年11月26日
     */
    private static Date getDateData(ExcelCell ec, String value) {
        if (!StringUtils.isEmpty(ec.dataFormatter()) && !StringUtils.isEmpty(value)) {
            SimpleDateFormat format = new SimpleDateFormat(ec.dataFormatter());
            try {
                return format.parse(value);
            } catch (ParseException e) {
                logger.error("时间格式化失败,格式化:{},值:{}", ec.dataFormatter(), value);
                throw new RuntimeException("", e);
            }
        }
        return null;
    }

}
